﻿Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports Tamir.SharpSsh
Imports log4net
Imports System.Threading
Imports System.Globalization

Partial Class ExportFrm
    Inherits System.Web.UI.Page

    Public scirpttext As String
    Private Shared logger As ILog = LogManager.GetLogger("ExportFrm")

    Protected Overrides Sub InitializeCulture()
        Dim selectedLanguage As String = "en-US"
        Thread.CurrentThread.CurrentCulture = _
            CultureInfo.CreateSpecificCulture(selectedLanguage)
        Thread.CurrentThread.CurrentUICulture = New  _
            CultureInfo(selectedLanguage)

        MyBase.InitializeCulture()
    End Sub

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Session("PlanYear") = "2557"
        If Session("users") Is Nothing Then
            Response.Redirect("LoginForm.aspx")
        End If
        'If Session("RoleType") Is Nothing Or Not (Val(Session("RoleType")) = 5) Then
        '    Dim x1 =Session("RoleType") 
        '    Response.Redirect("UnAuthorize.aspx")
        'End If

        PlanYearsTxt.Text = Session("PlanYear")
        Label1.Text = "ระบบจัดทำงบประมาณ > จัดทำงบประมาณ > Export"
        'FromVersionTxt.Text = "0"
    End Sub
    Protected Sub WebCombo1_InitializeDataSource(sender As Object, e As ISNet.WebUI.WebCombo.DataSourceEventArgs)
        Dim ds As New DataSet
        Dim dt As DataTable = ds.Tables.Add("Items")
        dt.Columns.Add("Code", Type.GetType("System.String"))
        dt.Columns.Add("Description", Type.GetType("System.String"))
        Dim r As DataRow
        r = dt.NewRow()
        r("Code") = "1"
        r("Description") = "Master IO"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "2"
        r("Description") = "Master Funded Program"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "3"
        r("Description") = "งบประมาณ"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "4"
        r("Description") = "ประมาณการรายได้"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "5"
        r("Description") = "แผนเบิกจ่ายจริง"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "6"
        r("Description") = "ข้อมูลการจัดกลุ่มศูนย์ต้นทุนตามแผนยุทธศาสตร์"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "7"
        r("Description") = "แผนยุทธศาสตร์"
        dt.Rows.Add(r)

        r = dt.NewRow()
        r("Code") = "8"
        r("Description") = "ข้อมูลการจัดกลุ่ม Commitment Item"
        dt.Rows.Add(r)

        e.DataSource = dt
    End Sub

    Protected Sub SearhBtn_Click(sender As Object, e As ImageClickEventArgs) Handles SearhBtn.Click
        Dim fp_adapter As New ExportTableAdapters.FundedProgramProjectsTableAdapter
        Dim fp_datatable As New Export.FundedProgramProjectsDataTable
        Dim fa_adapter As New ExportTableAdapters.FundedProgramAssetsTableAdapter
        Dim fa_datatable As New Export.FundedProgramAssetsDataTable
        Dim pj_adapter As New ExportTableAdapters.ProjectsTableAdapter
        Dim pj_datatable As New Export.ProjectsDataTable
        Dim bd_adapter As New ExportTableAdapters.BudgetDetailsTableAdapter
        Dim bd_datatable As New Export.BudgetDetailsDataTable
        Dim at_adapter As New ExportTableAdapters.ActivityTypesTableAdapter
        Dim at_datatable As New Export.ActivityTypesDataTable
        Dim tw_adapter As New ExportTableAdapters.Budget12TableAdapter
        Dim tw_datatable As New Export.Budget12DataTable

        Dim ExportText As String = ""
        Dim FundedProgram, fund As String
        Dim OrderType As String
        Dim RespFct As String
        Dim ActivityTypeCode, FunctionalArea As String
        Dim Description As String
        Dim EYear As String
        Dim desc, short_desc As String
        Dim budgetdetail_id As Decimal
        EYear = (Val(PlanYearsTxt.Text)).ToString

        Dim filename As String = Nothing

        If WebCombo1.Value = "1" Then
            fp_datatable = fp_adapter.GetDataByYearVersion(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            For Each fp_row As Export.FundedProgramProjectsRow In fp_datatable
                RespFct = ""
                FundedProgram = If(fp_row.IsFundedProgramNull, "", fp_row.FundedProgram)
                OrderType = ""
                If Left(FundedProgram, 1) = "P" Then
                    OrderType = "ZPJP"
                ElseIf Left(FundedProgram, 1) = "D" Then
                    OrderType = "ZPJD"
                ElseIf Mid(FundedProgram, 4, 1) <> "9" Then
                    OrderType = "ZA" & Left(FundedProgram, 2)
                ElseIf Mid(FundedProgram, 4, 1) = "9" Then
                    OrderType = "ZL" & Left(FundedProgram, 2)
                End If
                pj_datatable = pj_adapter.GetDataByCode(FundedProgram, Val(PlanYearsTxt.Text))
                RespFct = ""
                For Each pj_row As Export.ProjectsRow In pj_datatable
                    RespFct = If(pj_row.IsFundCenter_FundCenterCodeNull, "", pj_row.FundCenter_FundCenterCode)
                Next
                If RespFct = "" Then
                    bd_datatable = bd_adapter.GetDataByFundedProg(FundedProgram, FromVersionTxt.Text, Val(PlanYearsTxt.Text))
                    For Each bd_row As Export.BudgetDetailsRow In bd_datatable
                        RespFct = bd_row.FundCenter_FundCenterCode
                    Next
                End If
                ExportText += OrderType & Chr(9) & FundedProgram & Chr(9) & fp_row.Description & Chr(9) & "1000" & Chr(9) & RespFct & Chr(13) & Chr(10)
            Next

            fa_datatable = fa_adapter.GetDataByYearVersion(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            For Each fa_row As Export.FundedProgramAssetsRow In fa_datatable
                FundedProgram = If(fa_row.IsFundedProgramNull, "", fa_row.FundedProgram)
                If Left(FundedProgram, 1) = "P" Then
                    OrderType = "ZPJP"
                ElseIf Left(FundedProgram, 1) = "D" Then
                    OrderType = "ZPJD"
                ElseIf Mid(FundedProgram, 2, 1) <> "9" Then
                    OrderType = "ZA" & Left(FundedProgram, 2)
                ElseIf Mid(FundedProgram, 2, 1) = "9" Then
                    OrderType = "ZL" & Left(FundedProgram, 2)
                End If

                Try
                    budgetdetail_id = fa_row.BudgetDetail_Id
                Catch ex As Exception
                    budgetdetail_id = 0
                End Try

                RespFct = ""
                bd_datatable = bd_adapter.GetDataByID(budgetdetail_id)
                For Each pj_row As Export.BudgetDetailsRow In bd_datatable
                    RespFct = pj_row.FundCenter_FundCenterCode
                Next

                Description = ""
                ActivityTypeCode = ""
                For Each bd_row As Export.BudgetDetailsRow In bd_datatable
                    ActivityTypeCode = If(bd_row.IsActivityType_ActivityTypeCodeNull, "", bd_row.ActivityType_ActivityTypeCode)
                Next

                at_datatable = at_adapter.GetDataByCode(ActivityTypeCode, Val(PlanYearsTxt.Text))
                For Each at_row As Export.ActivityTypesRow In at_datatable
                    Description = at_row.Description
                Next
                If Len(Description) > 40 Then
                    Description = Left(Description, 40)
                End If
                ExportText += OrderType & Chr(9) & FundedProgram & Chr(9) & Description & Chr(9) & "1000" & Chr(9) & RespFct & Chr(13) & Chr(10)
            Next

            logger.Info("User " & Session("users") & " export IO file")

            Try
                httpDownload(ExportText)
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
                Exit Sub
            End Try
        ElseIf WebCombo1.Value = "2" Then
            fp_datatable = fp_adapter.GetDataByYearVersion(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            For Each fp_row As Export.FundedProgramProjectsRow In fp_datatable
                ExportText += If(fp_row.IsFundedProgramNull, "", fp_row.FundedProgram) & Chr(9) & "1000" & Chr(9) & fp_row.ShortDescription & Chr(9) & fp_row.Description & Chr(9) & "01.01." & EYear & Chr(9) & "31.12." & (CInt(EYear) + 4) & Chr(13) & Chr(10)
            Next
            fa_datatable = fa_adapter.GetDataByYearVersion(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            For Each fa_row As Export.FundedProgramAssetsRow In fa_datatable
                Try
                    budgetdetail_id = fa_row.BudgetDetail_Id
                Catch ex As Exception
                    budgetdetail_id = 0
                End Try
                bd_datatable = bd_adapter.GetDataByID(budgetdetail_id)

                Description = ""
                ActivityTypeCode = ""
                For Each bd_row As Export.BudgetDetailsRow In bd_datatable
                    ActivityTypeCode = If(bd_row.IsActivityType_ActivityTypeCodeNull, "", bd_row.ActivityType_ActivityTypeCode)
                Next
                at_datatable = at_adapter.GetDataByCode(ActivityTypeCode, Val(PlanYearsTxt.Text))
                For Each at_row As Export.ActivityTypesRow In at_datatable
                    Description = at_row.Description
                Next
                desc = Description
                short_desc = Description
                If Len(Description) > 50 Then
                    desc = Left(Description, 50)
                End If
                If Len(Description) > 20 Then
                    short_desc = Left(Description, 20)
                End If
                ' add "Valid From" and "Valid To" to 5 years 
                ExportText += If(fa_row.IsFundedProgramNull, "", fa_row.FundedProgram) & Chr(9) & "1000" & Chr(9) & short_desc & Chr(9) & desc & Chr(9) & "01.01." & EYear & Chr(9) & "31.12." & (CInt(EYear) + 4) & Chr(13) & Chr(10)
            Next

            logger.Info("User " & Session("users") & " export Funded Program file")

            Try
                httpDownload(ExportText)
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
                Exit Sub
            End Try
        ElseIf WebCombo1.Value = "3" Then
            tw_datatable = tw_adapter.GetData(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            ExportText += "Budg. Process" & Chr(9) & "PREP" & Chr(13) & Chr(10)
            ExportText += "FM Area" & Chr(9) & "1000" & Chr(13) & Chr(10)
            ExportText += "Ledger" & Chr(9) & "9F" & Chr(13) & Chr(10)
            ExportText += "Record Type" & Chr(9) & "1" & Chr(13) & Chr(10)
            ExportText += "Version" & Chr(9) & "F01" & Chr(13) & Chr(10)
            ExportText += "Fiscal Year" & Chr(9) & EYear & Chr(13) & Chr(10)
            ExportText += "BCS Value Type" & Chr(9) & "B1" & Chr(13) & Chr(10)
            ExportText += "Workflow Status" & Chr(9) & "P" & Chr(13) & Chr(10)
            ExportText += "Budget Type" & Chr(9) & "ORIG" & Chr(13) & Chr(10)
            ExportText += Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & "001" & Chr(9) & "002" & Chr(9) & "003" & Chr(9) & "004" & Chr(9) & "005" & Chr(9) & "006" & Chr(9) & "007" & Chr(9) & "008" & Chr(9) & "009" & Chr(9) & "010" & Chr(9) & "011" & Chr(9) & "012" & Chr(10)
            ExportText += "Fund" & Chr(9) & "Funds Center" & Chr(9) & "Commitment Item" & Chr(9) & "Funded Program" & Chr(9) & "Functional Area" & Chr(9) & "Jan" & Chr(9) & "Feb" & Chr(9) & "Mar" & Chr(9) & "Apr" & Chr(9) & "May" & Chr(9) & "Jun" & Chr(9) & "Jul" & Chr(9) & "Aug" & Chr(9) & "Sep" & Chr(9) & "Oct" & Chr(9) & "Nov" & Chr(9) & "Dec" & Chr(13) & Chr(10)
            For Each tw_row As Export.Budget12Row In tw_datatable
                fund = "1110"
                FunctionalArea = ""
                If If(tw_row.IsProject_ProjectCodeNull, "", tw_row.Project_ProjectCode) <> "" Then
                    FunctionalArea = tw_row.Project_ProjectCode
                    If Left(tw_row.Project_ProjectCode, 1) = "P" Then
                        fund = "1210"
                    ElseIf Left(tw_row.Project_ProjectCode, 1) = "D" Then
                        fund = "1220"
                    End If
                Else
                    If If(tw_row.IsActivityType_ActivityTypeCodeNull, "", tw_row.ActivityType_ActivityTypeCode) <> "" Then
                        FunctionalArea = tw_row.ActivityType_ActivityTypeCode
                        If Left(tw_row.ActivityType_ActivityTypeCode, 1) = "E" Then
                            fund = "1240"
                        Else
                            fund = "1230"
                        End If
                    End If
                End If
                FundedProgram = If(tw_row.IsFundedProgramNull, "", tw_row.FundedProgram)
                If FundedProgram = "" Then
                    FundedProgram = "OPEX"
                End If
                If FunctionalArea = "" Then
                    FunctionalArea = "99999999"
                End If

                ExportText += fund & Chr(9) & CInt(tw_row.FundCenter_FundCenterCode) & Chr(9) & tw_row.Cmmt_CmmtCode & Chr(9) & FundedProgram & Chr(9) & FunctionalArea & Chr(9) & Format((tw_row.amount1 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount2 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount3 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount4 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount5 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount6 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount7 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount8 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount9 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount10 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount11 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount12 * (-1)), "#.00") & Chr(13) & Chr(10)
            Next

            logger.Info("User " & Session("users") & " export budget file")

            Try
                httpDownload(ExportText)
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
                Exit Sub
            End Try
        ElseIf WebCombo1.Value = "4" Then
            Dim br_adapter As New ExportTableAdapters.BudgetRevPlansTableAdapter
            Dim br_datatable As New Export.BudgetRevPlansDataTable
            br_datatable = br_adapter.GetDataByYear(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            Dim prodadap As New ExportTableAdapters.ProductsTableAdapter
            Dim proitemadap As New ExportTableAdapters.ProductItemsTableAdapter
            For Each br_row As Export.BudgetRevPlansRow In br_datatable
                Dim ProductItemID As Decimal
                Dim FormID, CmmtCode As String
                Dim productcode, productdesc, proitemcode, proitemdesc As String
                productcode = (If(br_row.IsProduct_ProductCodeNull, "", br_row.Product_ProductCode))
                If productcode <> "" Then
                    Try
                        productdesc = prodadap.GetDescription(productcode, br_row.Product_PlanYear)
                    Catch ex As Exception
                        productdesc = ""
                    End Try
                Else
                    productdesc = ""
                End If
                ProductItemID = br_row.ProductItem_Id
                Dim proitemdt As New Export.ProductItemsDataTable
                proitemdt = proitemadap.GetDataById(ProductItemID)
                If proitemdt.Count > 0 Then
                    proitemcode = proitemdt.Item(0).ItemNo
                    proitemdesc = proitemdt.Item(0).Description
                Else
                    proitemcode = ""
                    proitemdesc = "'"
                End If
                FormID = br_adapter.GetFormID(ProductItemID)
                CmmtCode = br_adapter.GetCmmtCode(ProductItemID, FormID, Val(PlanYearsTxt.Text))
                ExportText += EYear & Chr(9) & ToVersionTxt.Text & Chr(9) & "" & Chr(9) & br_row.FundCenter_FundCenterCode & Chr(9) & CmmtCode & Chr(9) & productdesc & Chr(9) & proitemcode & Chr(9) & proitemdesc & Chr(9) & "" & Chr(9) & br_row.Product_ProductCode & Chr(9)
                ExportText += Format(br_row.CurYearMonth1, "#.00") & Chr(9) & Format(br_row.CurYearMonth2, "#.00") & Chr(9) & Format(br_row.CurYearMonth3, "#.00") & Chr(9) & Format(br_row.CurYearMonth4, "#.00") & Chr(9) & Format(br_row.CurYearMonth5, "#.00") & Chr(9) & Format(br_row.CurYearMonth6, "#.00") & Chr(9) & Format(br_row.CurYearMonth7, "#.00") & Chr(9) & Format(br_row.CurYearMonth8, "#.00") & Chr(9) & Format(br_row.CurYearMonth9, "#.00") & Chr(9) & Format(br_row.CurYearMonth10, "#.00") & Chr(9) & Format(br_row.CurYearMonth11, "#.00") & Chr(9) & Format(br_row.CurYearMonth12, "#.00") & Chr(13) & Chr(10)
            Next

            logger.Info("User " & Session("users") & " export estimation file")

            Try
                httpDownload(ExportText)
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
                Exit Sub
            End Try
        ElseIf WebCombo1.Value = "5" Then
            tw_datatable = tw_adapter.GetData(Val(PlanYearsTxt.Text), FromVersionTxt.Text)
            'ExportText += "Fund" & Chr(9) & "Funds Center" & Chr(9) & "Commitment Item" & Chr(9) & "Funded Program" & Chr(9) & "Functional Area" & Chr(9) & "Jan" & Chr(9) & "Feb" & Chr(9) & "Mar" & Chr(9) & "Apr" & Chr(9) & "May" & Chr(9) & "Jun" & Chr(9) & "Jul" & Chr(9) & "Aug" & Chr(9) & "Sep" & Chr(9) & "Oct" & Chr(9) & "Nov" & Chr(9) & "Dec" & Chr(13) & Chr(10)
            For Each tw_row As Export.Budget12Row In tw_datatable
                fund = "1110"
                FunctionalArea = ""
                If If(tw_row.IsProject_ProjectCodeNull, "", tw_row.Project_ProjectCode) <> "" Then
                    FunctionalArea = tw_row.Project_ProjectCode
                    If Left(tw_row.Project_ProjectCode, 1) = "P" Then
                        fund = "1210"
                    ElseIf Left(tw_row.Project_ProjectCode, 1) = "D" Then
                        fund = "1220"
                    End If
                Else
                    If If(tw_row.IsActivityType_ActivityTypeCodeNull, "", tw_row.ActivityType_ActivityTypeCode) <> "" Then
                        FunctionalArea = tw_row.ActivityType_ActivityTypeCode
                        If Left(tw_row.ActivityType_ActivityTypeCode, 1) = "E" Then
                            fund = "1240"
                        Else
                            fund = "1230"
                        End If
                    End If
                End If
                FundedProgram = If(tw_row.IsFundedProgramNull, "", tw_row.FundedProgram)
                If FundedProgram = "" Then
                    FundedProgram = "OPEX"
                End If
                If FunctionalArea = "" Then
                    FunctionalArea = "99999999"
                End If

                ExportText += tw_row.Version & Chr(9) & tw_row.PlanYear & Chr(9) & fund & Chr(9) & CInt(tw_row.FundCenter_FundCenterCode) & Chr(9) & tw_row.Cmmt_CmmtCode & Chr(9) & FundedProgram & Chr(9) & FunctionalArea & Chr(9) & Format((tw_row.amount1 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount2 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount3 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount4 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount5 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount6 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount7 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount8 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount9 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount10 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount11 * (-1)), "#.00") & Chr(9) & Format((tw_row.amount12 * (-1)), "#.00") & Chr(13) & Chr(10)
            Next

            logger.Info("User " & Session("users") & " export budget file")

            Try
                httpDownload(ExportText)
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
                Exit Sub
            End Try
        ElseIf WebCombo1.Value = "6" Then
            'sm_datatable = sm_adapter.GetData(Val(PlanYearsTxt.Text))
            Dim sm_adapter As New StrategyMappingDSTableAdapters.StrategyMappingTableAdapter
            Dim sm_datatable As New StrategyMappingDS.StrategyMappingDataTable
            sm_datatable = sm_adapter.GetDataByPlanYear(Val(PlanYearsTxt.Text))
            For Each sm_row As StrategyMappingDS.StrategyMappingRow In sm_datatable
                Dim sm_FundCenter, sm_CommitmentItem, sm_PlanYear, sm_FundedProgram, _
                    sm_Strategy, sm_CreateDate, sm_CreateBy, sm_UpdateDate, sm_UpdateBy As String

                sm_FundCenter = (If(sm_row.IsFundCenterNull, "", sm_row.FundCenter))
                sm_CommitmentItem = (If(sm_row.IsCommitmentItemNull, "", sm_row.CommitmentItem))
                sm_PlanYear = (If(sm_row.IsPlanYearNull, "", sm_row.PlanYear))
                sm_FundedProgram = (If(sm_row.IsFundedProgramNull, "", sm_row.FundedProgram))
                sm_Strategy = (If(sm_row.IsStrategyNull, "", sm_row.Strategy))
                sm_CreateDate = (If(sm_row.IsCreateDateNull, "", sm_row.CreateDate))
                sm_CreateBy = (If(sm_row.IsCreateByNull, "", sm_row.CreateBy))
                sm_UpdateDate = (If(sm_row.IsUpdateDateNull, "", sm_row.UpdateDate))
                sm_UpdateBy = (If(sm_row.IsUpdateByNull, "", sm_row.UpdateBy))

                ExportText += sm_PlanYear & "|" & sm_FundCenter & "|" & sm_CommitmentItem & "|" _
                              & sm_FundedProgram & "|" & sm_Strategy & Chr(13) & Chr(10)
            Next

            ExportText += vbNewLine & Format(sm_datatable.Count, "0") & " rows selected" & vbNewLine
            logger.Info("User " & Session("users") & " export estimation file")

            '// FTP Transfer
            Try
                Dim culture As New CultureInfo("en-US")

                filename = "S_BPMNEW_FUND_CENTER_STRATEGIC_MAP_" & "_" & DateTime.Now.ToString("yyyyMMdd", culture) & ".txt"

                Dim StrWer As New StreamWriter(Server.MapPath("Export") & "\" & filename, True, System.Text.Encoding.GetEncoding(874))
                logger.Info("User " & Session("users") & " create file name " & filename)

                StrWer.Write(ExportText)
                StrWer.Close()
                logger.Info("User " & Session("users") & " write file successfully.")

                sftpupload(Server.MapPath("Export") & "\" & filename, filename)
                'sftpupload("C:" & "\" & filename, filename)
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้ (FTP) : " & ex.ToString().Substring(0, 15) & "')</script>"
                Exit Sub
            End Try

            'ManopN
        ElseIf WebCombo1.Value = "7" Then 'Strategy
            Dim ACTIVE_PLAN_YEAR As Integer
            ACTIVE_PLAN_YEAR = Val(PlanYearsTxt.Text) ' Val(ConfigurationManager.AppSettings("ACTIVE_PLAN_YEAR"))


            Dim br_adapter As New StrategyDSTableAdapters.StrategyTableAdapter()
            Dim br_datatable As New StrategyDS.StrategyDataTable
            br_datatable = br_adapter.GetDataByYear(ACTIVE_PLAN_YEAR)


            For Each br_row As StrategyDS.StrategyRow In br_datatable

                Dim StrategyCode As String
                Dim DescriptionST As String



                StrategyCode = br_row.StrategyCode

                DescriptionST = (If(br_row.IsDescriptionNull, "", br_row.Description))

                ExportText += ACTIVE_PLAN_YEAR & "|" & StrategyCode & "|" & DescriptionST & vbNewLine

            Next

            ExportText += vbNewLine & Format(br_datatable.Count, "0") & " rows selected" & vbNewLine

            logger.Info("User " & Session("users") & " export Strategy file")

            '// FTP Transfer
            Try
                Dim culture As New CultureInfo("en-US")

                filename = "S_BPMNEW_STRATEGIC_PLAN" & "_" & DateTime.Now.ToString("yyyyMMdd", culture) & ".txt"

                Dim StrWer As New StreamWriter(Server.MapPath("Export") & "\" & filename, True, System.Text.Encoding.GetEncoding(874))
                logger.Info("User " & Session("users") & " create file name " & filename)

                StrWer.Write(ExportText)
                StrWer.Close()
                logger.Info("User " & Session("users") & " write file successfully.")

                sftpupload(Server.MapPath("Export") & "\" & filename, filename)
                'sftpupload("C:" & "\" & filename, filename)

            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้ (FTP) : " & ex.ToString().Substring(0, 15) & "')</script>"
                Exit Sub
            End Try

            'Chatwut
        ElseIf WebCombo1.Value = "8" Then

            Dim ch_adapter As New Export_CmmHierarchiesTableAdapters.CmmtHierarchiesTableAdapter
            Dim ch_datatable As New Export_CmmHierarchies.CmmtHierarchiesDataTable
            ch_datatable = ch_adapter.GetData(Val(PlanYearsTxt.Text))
            For Each ch_row As Export_CmmHierarchies.CmmtHierarchiesRow In ch_datatable
                Dim ch_HierarchieDescription, ch_CmmtsDescription, _
                    ch_Cmmt_CmmtCode, ch_ParentCmmtHierarchy_Id As String

                ch_HierarchieDescription = (If(ch_row.IsHierarchieDescriptionNull, "", ch_row.HierarchieDescription))
                ch_CmmtsDescription = (If(ch_row.IsCmmtsDescriptionNull, "", ch_row.CmmtsDescription))
                ch_Cmmt_CmmtCode = (If(ch_row.IsCmmt_CmmtCodeNull, "", ch_row.Cmmt_CmmtCode))
                ch_ParentCmmtHierarchy_Id = (If(ch_row.IsParentCmmtHierarchy_IdNull, "", ch_row.ParentCmmtHierarchy_Id))

                ExportText += ch_row.Id & "|" & ch_row.PlanYear & "|" & ch_row.CmmtGroup & "|" & ch_row.Level & "|" _
                              & ch_HierarchieDescription & "|" & ch_Cmmt_CmmtCode & "|" _
                              & ch_CmmtsDescription & "|" & ch_ParentCmmtHierarchy_Id & Chr(13) & Chr(10)
            Next

            ExportText += vbNewLine & Format(ch_datatable.Count, "0") & " rows selected" & vbNewLine
            logger.Info("User " & Session("users") & " export estimation file")

            '// FTP Transfer
            Try
                Dim culture As New CultureInfo("en-US")

                filename = "S_BPMNEW_COMMITMENT_ITEM_GROUP_" & "_" & DateTime.Now.ToString("yyyyMMdd", culture) & ".txt"

                Dim StrWer As New StreamWriter(Server.MapPath("Export") & "\" & filename, True, System.Text.Encoding.GetEncoding(874))
                logger.Info("User " & Session("users") & " create file name " & filename)

                StrWer.Write(ExportText)
                StrWer.Close()
                logger.Info("User " & Session("users") & " write file successfully.")

                sftpupload(Server.MapPath("Export") & "\" & filename, filename)
                'sftpupload("C:" & "\" & filename, filename)

            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)

                scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้ (FTP) : " & ex.ToString().Substring(0, 15) & "')</script>"
                Exit Sub
            End Try
        End If

        '// FTP Transfer
        ''Dim StrWer As StreamWriter
        'Try
        'Dim filename As String = FileNameTxt.Text & "_" & Format(Date.Now, "dd-MM-yyyy-HH_mm_ss") & ".txt"
        'Dim StrWer As New StreamWriter(Server.MapPath("Export") & "\" & filename, True, System.Text.Encoding.UTF8)
        ''StrWer = File.CreateText(Server.MapPath("Export") & "\" & filename)
        'StrWer.Write(ExportText)
        'StrWer.Close()
        'sftpupload(Server.MapPath("Export") & "\" & filename, filename)
        'Catch ex As Exception
        'scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
        'Exit Sub
        'End Try

        '// Http Download
        'Try
        'httpDownload(ExportText)
        'Catch ex As Exception
        'scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
        'Exit Sub
        'End Try

        'Response.Clear()
        'Response.AddHeader("content-disposition", "attachment;filename=Export.txt")
        'Response.Buffer = True
        'Response.Charset = "UTF-8"
        'Response.Cache.SetCacheability(HttpCacheability.NoCache)
        'Response.ContentType = "application/vnd.text"

        'Dim stringWrite As New System.IO.StringWriter()
        'Dim htmlWrite = New HtmlTextWriter(stringWrite)

        'Response.Write(ExportText)

    End Sub

    Sub httpDownload(ByVal _expText As String, Optional ByVal filename As String = Nothing)
        Dim success As Boolean = False

        Try
            Response.Clear()
            If filename = "" Then
                Response.AddHeader("content-disposition", "attachment;filename=Export.txt")
            Else
                Response.AddHeader("content-disposition", "attachment;filename=" & filename)
            End If

            Response.Buffer = True
            Response.Charset = "UTF-8"
            'set encoding as Unicode due to cannot read Thai characters on SAP system
            Response.ContentEncoding = Encoding.UTF8
            'Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = "application/vnd.text"

            Dim stringWrite As New System.IO.StringWriter()
            Dim htmlWrite = New HtmlTextWriter(stringWrite)

            Response.Write(_expText)
            Response.End()

            success = True
        Catch ex As Exception
            success = False
        End Try
    End Sub


    Sub httpDownload(ByVal _expText As String)
        Dim success As Boolean = False

        Try
            Response.Clear()
            Response.AddHeader("content-disposition", "attachment;filename=Export.txt")
            Response.Buffer = True
            Response.Charset = "UTF-8"
            'set encoding as Unicode due to cannot read Thai characters on SAP system
            Response.ContentEncoding = Encoding.UTF8
            'Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = "application/vnd.text"

            Dim stringWrite As New System.IO.StringWriter()
            Dim htmlWrite = New HtmlTextWriter(stringWrite)

            Response.Write(_expText)


            success = True

            logger.Info("User " & Session("users") & " download file successfully.")
            Response.End()
        Catch ex As Exception
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)

            success = False

            logger.Info("User " & Session("users") & " download file failed.")
        End Try

        'If success = True Then
        '    scirpttext = "<script>alert('ส่งออกไฟล์สำเร็จ');</script>"
        'Else
        '    scirpttext = "<script>alert('พบปัญหาในการโอนถ่ายไฟล์ ไม่สามารถส่งออกไฟล์ได้');</script>"
        'End If
    End Sub

    'Sub sftpupload(ByVal localfile As String, ByVal remotefile As String)
    '    'Dim input As New SshConnectionInfo
    '    Dim sshCp As SshTransferProtocolBase
    '    Dim hosturl As String = ConfigurationManager.AppSettings("sftpurl")
    '    Dim username As String = ConfigurationManager.AppSettings("sftpusr")
    '    Dim password As String = ConfigurationManager.AppSettings("sftppwd")
    '    sshCp = New Sftp(hosturl, username, password)
    '    Dim success As Boolean = False
    '    Try
    '        sshCp.Connect()
    '        sshCp.Put(localfile, ConfigurationManager.AppSettings("sftpdestfolder") & remotefile)
    '        success = True
    '    Catch ex As Exception
    '        sshCp.Cancel()
    '        success = False
    '    Finally
    '        sshCp.Close()
    '    End Try

    '    Try
    '        System.IO.File.Delete(localfile)
    '    Catch ex As Exception
    '        logger.Error(ex.Message)
    '        logger.Error(ex.StackTrace)
    '    End Try

    '    If success = True Then
    '        scirpttext = "<script>alert('ส่งออกไฟล์สำเร็จ')</script>"
    '    Else
    '        scirpttext = "<script>alert('พบปัญหาในการโอนถ่ายไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
    '    End If
    'End Sub

    'Public Structure SshConnectionInfo
    '    Public Host As String
    '    Public User As String
    '    Public Pass As String
    '    Public IdentityFile As String
    'End Structure

    Sub sftpupload(ByVal localfile As String, ByVal remotefile As String)
        'Dim input As New SshConnectionInfo
        Dim sshCp As SshTransferProtocolBase
        Dim hosturl As String = ConfigurationManager.AppSettings("sftpurl")
        Dim username As String = ConfigurationManager.AppSettings("sftpusr")
        Dim password As String = ConfigurationManager.AppSettings("sftppwd")
        Dim identity As String = ConfigurationManager.AppSettings("sftpIdentityFile")

        logger.Info("User " & Session("users") & " sftpusrl = " & hosturl)
        logger.Info("User " & Session("users") & " sftpusr = " & username)
        logger.Info("User " & Session("users") & " sftppwd = " & password)
        logger.Info("User " & Session("users") & " identity file = " & identity)

        'sshCp = New Sftp(hosturl, username, password)
        sshCp = New Sftp(hosturl, username)
        If password <> "" Then
            sshCp.Password = password
        End If
        If identity <> "" Then
            sshCp.AddIdentityFile(identity)
        End If

        Dim success As Boolean = False
        Try
            sshCp.Connect()
            sshCp.Put(localfile, ConfigurationManager.AppSettings("sftpdestfolder") & remotefile)
            success = True

            logger.Info("User " & Session("users") & " upload file successfully.")
        Catch ex As Exception
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)

            sshCp.Cancel()
            success = False

            logger.Info("User " & Session("users") & " upload file failed.")
        Finally
            sshCp.Close()
        End Try

        'Try
        'System.IO.File.Delete(localfile)
        'Catch ex As Exception
        'logger.Error(ex.Message)
        'logger.Error(ex.StackTrace)
        'End Try

        If success = True Then
            scirpttext = "<script>alert('ส่งออกไฟล์สำเร็จ')</script>"
        Else
            scirpttext = "<script>alert('พบปัญหาในการโอนถ่ายไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
        End If
    End Sub

    Public Structure SshConnectionInfo
        Public Host As String
        Public User As String
        Public Pass As String
        Public IdentityFile As String
    End Structure

End Class
